#!/usr/bin/python
# encoding: utf-8
'''
@File    :   code.py
@Time    :   2020/03/11 21:54:19
@Author  :   fangkaijin 
@Version :   1.0
@Contact :   fangkaijin@gmail.com
@License :   杭州移动互联
@Desc    :   None
'''

# 导入库包

import sys, os
import sqlite3

class SqlitOperator(object):
    def __init__(self, dbName):
        self.dbName = dbName
        print('创建sqlite成功')
        conn = sqlite3.connect(dbName)
        self.conn = conn

    def createTable(self, sql):
        print('创建表格')
        c = self.conn.cursor()
        c.execute(sql)
        self.conn.commit()
        c.close()

    def insertValue(self, sql):
        print('插入数据')
        c = self.conn.cursor()
        c.execute(sql)
        self.conn.commit()
        c.close()

    def closeConn(self):
        print('关闭连接')
        self.conn.close()

    def quryAll(self, sql):
        print('查询数据')
        c = self.conn.cursor()
        cursor = c.execute(sql)
        return cursor

    def toString(self, cursor):
        for row in cursor:
            print('ID= %d .  NAME= %s .  AGE= %d .  ADDRESS= %s . SALARY= %f .' % (row[0], row[1], row[2], row[3], row[4]))
        cursor.close()

    def updateValue(self, sql):
        print('更新数据')
        c = self.conn.cursor()
        c.execute(sql)
        self.conn.commit()
        c.close()

    def deleteValue(self, sql):
        print('删除数据')
        c = self.conn.cursor()
        c.execute(sql)
        self.conn.commit()
        c.close()

if __name__ == '__main__':
    sqlTest = SqlitOperator('test.db')
    sqlTest.createTable('''CREATE TABLE IF NOT EXISTS COMPANY
                (ID INTEGER PRIMARY KEY,
                NAME TEXT NOT NULL UNIQUE,
                AGE INT NOT NULL,
                ADDRESS CHAR(50),
                SALARY REAL);''')
    sqlTest.insertValue("INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('方开金3', 32, '杭州，余杭', 25000)")
    sqlTest.insertValue("INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('Aaron3', 32, '杭州，余杭', 25000)")
    cursor = sqlTest.quryAll("SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY")
    sqlTest.toString(cursor)
    sqlTest.closeConn()


